package com.supermap.dataservice.disaster.dao;

import com.supermap.dubbo.api.disaster.entity.Disaster;
import com.supermap.dubbo.api.disaster.entity.DisasterComment;
import com.supermap.dubbo.api.disaster.entity.DisasterDIP;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @Author:langhongshuai
 * @Description:病虫害数据访问层
 * @Date:18/1/29 下午2:42
 */
@Mapper
public interface DisasterDIPDao {
    /**
     * 获得用户的所有病虫害信息
     * @return
     */
    @Select("select t3.* ,t4.name as cropname from (select t1.*,t2.username from t_disaster_dip t1 \\n\" +\n" +
            "            \"inner JOIN t_user t2 on t1.userId=t2.id) t3\\n\" +\n" +
            "            \"inner JOIN t_crop t4 on t3.type=t4.code " +
            "              left join t_farmland t5 on t5.id = t4.farmlandid " +
            "where IF(ISNULL(#{userId}),true,t3.userId=#{userId}) and IF(ISNULL(#{areaCode}),true,t3.areacode like #{areaCode})")
    public List<DisasterDIP> getDisasterDIPByUIdArea(@Param("userId") String userId, @Param("areaCode") String areaCode);

    /**
     *增加病虫害
     * @param disasterdip
     */
    @Insert("insert into t_disaster_dip(userid,farmlandid,type,breed,growthperiod,longitude,latitude,areacode,description,img_url,disastertype,recordtime,development)\n " +
            "values(#{disasterdip.userId},#{disasterdip.farmlandid},#{disasterdip.type},#{disasterdip.breed},#{disasterdip.growthperiod},\n" +
            "#{disasterdip.longitude},#{disasterdip.latitude},#{disasterdip.areacode},#{disasterdip.description},#{disasterdip.imgUrl},\n" +
            "#{disasterdip.disastertype},#{disasterdip.recordtime},#{disasterdip.development})")
    public void addDisasterDIP(@Param("disasterdip") DisasterDIP disasterdip);


    /**
     * 获取所有的病虫害信息
     * @return
     */
    @Select("select t3.* ,t4.`name` as cropname,t5.farmname from (select t1.*,t2.username from t_disaster_dip t1 \n" +
            "Inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "Inner JOIN t_crop t4 on t3.type=t4.code" +
            "left join t_farmland t5 on t3.farmlandid = t5.id order by t3.recordtime desc;")
    public List<DisasterDIP> getAllDisasterDIP();


    /**
     * 删除病虫害信息
     * @param disdipId
     */
    @Delete("Delete from t_disaster_dip where id=#{disdipId}")
    public void delDisasterDIP(@Param("disdipId") int disdipId);

    /**
     * 根据区域编码获取所有的病虫害信息
     * @return
     */
    @Select("select t3.* ,t4.`name` as cropname,t5.farmname from (select t1.*,t2.username from t_disaster t1 \n" +
            " Inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            " Inner JOIN t_crop t4 on t3.type=t4.id \n" +
            " left join t_farmland t5 on t5.id = t3.farmlandid " +
            " where t3.areacode =#{areacode}")

    public List<DisasterDIP> getDisasterByareacode(@Param("areacode") String areacode);

    /**
     * 根据type得到病虫害信息
     * @param type
     * @return
     */
    @Select("select t1.*,t2.username as username,t3.areaname as areaname,t4.farmname from t_disaster t1 left join t_user t2 on t1.userId = t2.id left join t_area t3 on t3.areacode = t1.areacode left join t_farmland t4 on t4.id = t1.farmlandid where if((isnull(#{type}) or #{type}=''),true,t1.type in(select id from t_crop where id in(select id from t_crop where parentid = #{type} or id= #{type}) or"
            +" parentid in (select id from t_crop where parentid = #{type} ))) and if(isnull(#{areaCode}),true,t1.areacode like CONCAT(#{areaCode},'%'))  and if(isnull(#{disasterType}) or #{disasterType}='',true,t1.disastertype = #{disasterType}) and if(isnull(#{dateTime}),true,t1.createTime like CONCAT(#{dateTime},'%'))")
    List<DisasterDIP> getDisasterDIP(@Param("type") String type, @Param("areaCode") String areaCode, @Param("disasterType") String disasterType, @Param("dateTime") String dateTime);

    /**
     * 通过农田id获取所有的病虫害信息
     * @return
     */
    @Select("select t3.* ,t4.`name` as cropname,t5.farmname,t6.name as typename from (select t1.*,t2.username from t_disaster_dip t1 \n" +
            "Inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "Inner JOIN t_crop t4 on t3.type=t4.code " +
            "left join t_farmland t5 on t3.farmlandid = t5.id  " +
            "left join t_disastertype t6 on t6.code = t3.disastertype " +
            "where t3.farmlandid=#{farmlandid} order by t3.recordtime desc;")
    public List<DisasterDIP> getAllDisasterDIPByFarmlandId(@Param("farmlandid")int farmlandid);

    /**
     * 通过作物code获取所有的病虫害信息
     * @return
     */
    @Select("select t3.* ,t4.`name` as cropname,t5.farmname,t6.name as typename  from (select t1.*,t2.username from t_disaster_dip t1 \n" +
            "Inner JOIN t_user t2 on t1.userId=t2.id) t3\n" +
            "Inner JOIN t_crop t4 on t3.type=t4.code " +
            "left join t_farmland t5 on t3.farmlandid = t5.id " +
            " left join t_disastertype t6 on t6.code = t3.disastertype " +
            " where t3.type=#{cropCode} and t3.userid = #{userId} order by t3.recordtime desc;")
    public List<DisasterDIP> getAllDisasterDIPByCCodeAndUId(@Param("cropCode")String cropCode,@Param("userId")int userId);
}
